package com.yalu.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yalu.entity.data.Expressfees;
import com.yalu.entity.data.ExpressfeesKey;

public class ExpressfeesExcelImportUtil extends BaseExcelImportUtil {
	private List<List<String>> str = null;
	private static final String TITLE = "仓库代码,仓库名称,快递代码,快递公司,收寄日期,快递单号,"
			+ "到达省份（直辖市）,市（地级市）,县级市（区）,乡镇（村）,重量,邮费,备注";
	private static int COLUMN_COUNT = 13;

	public ExpressfeesExcelImportUtil() {

	}

	@Override
	public List<String> importExcel(String excelFile) {
		try {
			List<List<String>> array = readExcelReturnArray(excelFile);
			return validate(array);
		} catch (Exception e) {
			e.printStackTrace();
			return errMsg;
		}

	}

	private List<String> validate(List<List<String>> array) {
		for (int rowIndex = 0; rowIndex < array.size(); rowIndex++) {
			List<String> row = array.get(rowIndex);
			if (rowIndex == 0) {
				String msg = validateTitle(row);
				if (StringUtils.isNotBlank(msg)) {
					errMsg.add(msg);
				}
			} else {
				String msg = validateData(rowIndex, row);
				if (StringUtils.isNotBlank(msg)) {
					errMsg.add(msg);
				}
			}
		}
		return errMsg;
	}

	/**
	 * @Description: 数据格式校验
	 * @param rowIndex
	 * @param row
	 * @return
	 * @date 2013-10-29 上午12:21:12
	 * @author zch
	 */
	private String validateData(int rowIndex, List<String> row) {
		Expressfees entity = new Expressfees();
		ExpressfeesKey expressfeesKey = new ExpressfeesKey();
		String msg = "";
		String value = "";
		for (int columnIndex = 0; columnIndex < COLUMN_COUNT; columnIndex++) {
			value = row.get(columnIndex);

			switch (columnIndex) {
			case 0:
				if (StringUtils.isNotBlank(value)) {
					entity.setWh_code(NumberUtils.toInt(value));
				} else {
					msg = "【仓库代码】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 1:
				if (StringUtils.isNotBlank(value)) {
					entity.setWh_name(value);
				} else {
					msg = "【仓库名称】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
			case 2:
				if (StringUtils.isNotBlank(value)) {
					expressfeesKey.setExp_code(value);
				} else {
					msg = "【快递代码】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 3:
				if (StringUtils.isNotBlank(value)) {
					entity.setExp_name(value);
				} else {
					msg = "【快递公司】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 4:
				if (StringUtils.isNotBlank(value)) {
					entity.setExp_date(value);
				} else {
					msg = "【收寄日期】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				
				try {
					SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
					formatter.setLenient(false);
					formatter.format(formatter.parse(value));
					entity.setExp_date(value);
				} catch (Exception e) {
					msg = "【时间】栏第【" + rowIndex + "】笔数据格式不正确";
					//提示信息：XX栏第XX笔与第XX笔，XX同一个店铺的日期不可以重复
					return msg;
				}
				
				break;

			case 5:
				if (StringUtils.isNotBlank(value)) {
					expressfeesKey.setExp_id(value);
				} else {
					msg = "【快递单号】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 6:
				if (StringUtils.isNotBlank(value)) {
					entity.setLocation(value);
				} else {
					msg = "【到达省份（直辖市）】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 7:
				if (StringUtils.isNotBlank(value)) {
					entity.setArea(value);
				}
				break;
			case 8:
				if (StringUtils.isNotBlank(value)) {
					entity.setCity(value);
				}
				break;
			case 9:
				if (StringUtils.isNotBlank(value)) {
					entity.setDistrict(value);
				}
				break;
			case 10:
				if (StringUtils.isNotBlank(value)) {
					entity.setWeight(NumberUtils.toFloat(value));
				} else {
					msg = "【重量】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 11:
				if (StringUtils.isNotBlank(value)) {
					entity.setFee(NumberUtils.toFloat(value));
				} else {
					msg = "【邮费】栏第【" + rowIndex + "】笔数据不能为空";
					return msg;
				}
				break;
			case 12:
				if (StringUtils.isNotBlank(value)) {
					entity.setNote(value);
				}
				break;
			}
			entity.setExpressfeesKey(expressfeesKey);
		}
		dataList.add(entity);
		return null;
	}

		/**
		 * @Description: 校验标题行
		 * @param row
		 * @return
		 * @date 2013-10-29 上午12:19:39
		 * @author zch
		 */
		private String validateTitle(List<String> row) {
			String msg = "";
			String title = "";
			for (int index = 0; index < COLUMN_COUNT; index++) {
				if (index != COLUMN_COUNT - 1) {
					title = title + row.get(index) + ",";
				} else {
					title = title + row.get(index);
				}

			}
			if (!title.equals(TITLE)) {
				msg = "首行标题依次应为--" + TITLE;
				return msg;
			} else {
				return null;
			}
		}

		public List<List<String>> readExcelReturnArray(String excelFile)
				throws Exception {
			List<List<String>> array = new ArrayList<List<String>>();
			InputStream stream = null;
			try {
				stream = new FileInputStream(excelFile);
				XSSFWorkbook myWorkBook = new XSSFWorkbook(stream);
				XSSFSheet mySheet = myWorkBook.getSheetAt(0);
				Iterator<Row> rowIter = mySheet.rowIterator();
				String cellValue = "";
				int rowIndex = 0;
				while (rowIter.hasNext()) {
					XSSFRow myRow = (XSSFRow) rowIter.next();
					if(myRow==null){
						continue;
					}
					// Iterator<Cell> cellIter = myRow.cellIterator();
					List<String> rowArr = new ArrayList<String>();

					for (int cellnum = 0; cellnum <= COLUMN_COUNT; cellnum++) {
						cellValue = "";
						XSSFCell myCell = myRow.getCell(cellnum);
						if (myCell == null) {
							rowArr.add(cellValue.trim());
							continue;
						}
						// int columnIndex = myCell.getColumnIndex();

						switch (myCell.getCellType()) {
						case XSSFCell.CELL_TYPE_NUMERIC:
							Double numCellValue = myCell.getNumericCellValue();
							boolean isDate = DateUtil.isCellDateFormatted(myCell);
							if (DateUtil.isCellDateFormatted(myCell)) {
								Date t = myCell.getDateCellValue();
								SimpleDateFormat format = new SimpleDateFormat(
										"yyyyMMdd");
								cellValue = format.format(t);
							} else {
								cellValue = numCellValue.intValue() + "";//日期特殊处理
							}
							break;
						case XSSFCell.CELL_TYPE_STRING:
							String strCellString = myCell.getStringCellValue();
							cellValue = strCellString;
							break;
						case XSSFCell.CELL_TYPE_BLANK:
							break;
						}
						rowArr.add(cellValue.trim());
					}
					for (int columnIndex = COLUMN_COUNT; columnIndex > rowArr
							.size();) {
						rowArr.add("");
					}
					//不加入空行数据
					for(String str : rowArr){
						if(!str.equals("")){
							array.add(rowArr);
							break;
						}
					}
					rowIndex++;
				}
			} catch (FileNotFoundException e) {
				e.printStackTrace();
				throw e;
			} catch (IOException e) {
				e.printStackTrace();
				throw e;
			} finally {
				if (stream != null) {
					try {
						stream.close();
					} catch (IOException e1) {
						e1.printStackTrace();
					}
				}
			}

			return array;
		}

		public List<List<String>> getStr() {
			return str;
		}

		public void setStr(List<List<String>> str) {
			this.str = str;
		}

	}
